package com.zfzs.post.modules.agent.repository;

import com.zfzs.post.modules.agent.domain.Agent;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;

import java.util.List;
import java.util.Map;

/**
 * @author ck
 * @date 2019-08-07
 */
public interface AgentRepository extends JpaRepository<Agent, Long>, JpaSpecificationExecutor {
    //根据手机号码 和渠道id查询
    Agent findByPhoneAndChannelId(String phone, Long channelId);

    //根据ID查询
    Agent findByid(Long id);

    //根据pidId查询
    List<Agent> findByPidId(Long id);

    //根据姓名或者手机号码模糊查询
    @Query(value = "select * from agent agent0_ left outer join agent_verified " +
            "agentinfo1_ on agent0_.id=agentinfo1_.id where agent0_.phone = ?1 and agent0_.id in (?2) or agentinfo1_.card_name = ?1 and agent0_.id in (?2)", nativeQuery = true)
    List<Agent> findByPhoneOrNickName(String keywords, List<Long> ids);

    @Query(value = "select * from agent agent0_ left outer join agent_verified " +
            "agentinfo1_ on agent0_.id = agentinfo1_.id where agent0_.id in (?1)", nativeQuery = true)
    List<Agent> findByIds(List<Long> ids);

    //查询是否是自己的下级直属
    @Query(value = "select  COUNT(id) from agent where id = ?1 and pid_id = ?2", nativeQuery = true)
    int countDirectly(Long id, Long pidId);

    //根据时间、代理商查询数量
    @Query(value = "SELECT count(id) FROM agent WHERE channel_id = ?1 AND id IN (?2) AND create_date >= ?3 AND create_date <= ?4", nativeQuery = true)
    int queryCountByAgentIds(Long channelId, List<Long> ids, String startDate, String endDate);

    //商户新增明细
    @Query(value = "SELECT " +
            "a.phone AS phone," +
            "a.create_date AS createDate," +
            "a.pid_id AS pidId," +
            "a.id AS agentId," +
            "a.is_effective AS isEffective," +
            "if((select count(i.agent_id) from transaction_info i where i.agent_id = a.id) = 0,'暂无交易','有交易') as isTransaction " +
            "FROM " +
            "agent a " +
            "WHERE " +
            "a.id IN (?1) and a.channel_id = ?2 and a.create_date >= ?3 and a.create_date <= ?4 order by a.create_date desc ", nativeQuery = true)
    List<Map<String, Object>> queryAddAgentList(List<Long> ids,Long channelId,String startDate,String endDate);

    @Query(value = "select * from agent a where a.channel_id = ?1 and a.pid_id = 0", nativeQuery = true)
    Agent queryAgentTop(Long channelId);
}